Dimension Table Selection Strategies to Referential Partition a Fact Table of Relational Data Warehouses

Enterprise wide data warehouses are becoming increasingly adopted as the main source and underlying infrastructure for business intelligence (BI) solutions. Note that a data warehouse can be viewed as an integration system, where data sources are duplicated in the same repository. Data warehouses are designed to handle the queries required to discover trends and critical factors are called Online Analytical Processing (OLAP) systems. Examples of an OLAP query are: Amazon (www. amazon.com) company analyzes purchases by its customers to come up with an individual screen with products of likely interest to the customer. Analysts at WalMart (www.walmart.com) look for items with increasing sales in some city. Star schemes or their variants are usually used to model warehouse applications. They are composed of thousand of dimension tables and multiple fact tables [15, 18]. Figure 2.1 shows an example of star schema of the widely-known data warehouse benchmark APB-1 release II [21]. Here, the fact table Sales is joint to the following four dimension tables: Product, Customer, Time, Channel. Star queries are typically executed against the warehouse. Queries running on such applications contain a large number of costly joins, selections and aggregations. They are called mega queries [24]. To optimize these queries, the use of advanced optimization techniques is necessary. By analyzing the most important optimization techniques studied in the literature and supported by the most important commercial Database Management Systems (DBMS) like Oracle, SQL Server, DB2, etc. we propose to classify them into two main categories: (1) optimization techniques selected during the creation of data warehouses and (2) optimization techniques selected after the creation of the warehouses. The decision of choosing techniques in the first category is